I have recovered a table from MS Access inside SQL Server 2008 R2.
The old table in MS Access was using a PK with replication type integer (so there's negative values)
After importing the table into SQL Server 2008 R2, I have changed the PK for an integer identity column.
What I'm trying to do now is to make a script that will take all the row with negative PK, duplicate that row with a new PK (autonumber) and change the value of other table FK (negative values) with the newly created PK (autonumber). Then, finally, delete the OLD row that was just duplicate.
Thanks for the help
This is an example of the table :
CREATE TABLE [dbo].[TESTCopy](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Field1] [nchar](10) NOT NULL,
[Field2] [nchar](10) NOT NULL,
[Field3] [nchar](10) NOT NULL,
CONSTRAINT [PK_TESTCopy] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]